/***
This do-file creates a clean dataset to be read by the R code that generates the
low-wage employment ZIP-level maps.
***/

*-------------------------------------------------------------------------------
* Set up
*-------------------------------------------------------------------------------

* Set $root 
project figstabs, root
if (r(buildrunning)==0) include "${root}/code/config_interactive.do"

* Set globals
include "${root}/code/set_globals.do"

* Create required subfolders
cap mkdir "${root}/data/derived/Employment"
cap mkdir "${root}/results/Employment"

*-------------------------------------------------------------------------------
* Load and process Earnin data
*-------------------------------------------------------------------------------

project, uses("${root}/data/web/data/Earnin - ZCTA - 2020.csv")
import delimited "${root}/data/web/data/Earnin - ZCTA - 2020.csv", clear

* Make a percentage change
replace emp_incq1_apr2020 = (emp_incq1_apr2020 - 1) * 100

preserve
	project, uses("${root}/data/dvc/Crosswalks/zcta_cbsa_rel_10.txt")
	import delimited "${root}/data/dvc/Crosswalks/zcta_cbsa_rel_10.txt", clear
	rename zcta5 zcta
	keep zcta cbsa
	keep if inlist(cbsa, 16980, 35620, 41860)
	gen cbsa_name = "Chicago-Naperville-Elgin, IL-IN-WI" if cbsa == 16980
	replace cbsa_name = "New York-Newark-Jersey City, NY-NJ-PA" if cbsa == 35620
	replace cbsa_name = "San Francisco-Oakland-Hayward, CA" if cbsa == 41860
	
	tempfile zcta_cbsa
	save `zcta_cbsa'
restore

* Merge in CBSAs for finding the new areas
merge 1:1 zcta using `zcta_cbsa', keep(1 3) nogen keepusing(cbsa cbsa_name)

replace cbsa_name = "New York City" if cbsa_name == "New York-Newark-Jersey City, NY-NJ-PA"
replace cbsa_name = "Chicago" if cbsa_name == "Chicago-Naperville-Elgin, IL-IN-WI"
replace cbsa_name= "San Francisco"  if cbsa_name == "San Francisco-Oakland-Hayward, CA"
keep if !mi(cbsa_name)

* Select the ZCTAs to retain
gen city = ""

foreach city in "Chicago" "New York City" "San Francisco" {

	* Get the right CBSAs 
	levelsof cbsa if cbsa_name == "`city'", local(cbsas)
	local correct_cbsas = subinstr("`cbsas'", " ", ", ", .)
	noi di `correct_cbsas'

	* Mark the ZCTAs to expand
	gen mark_ind = inlist(cbsa, `correct_cbsas')
	
	* Create the city obs
	expand 2 if mark_ind == 1, gen(expand_ind)
	replace city = "`city'" if expand_ind == 1
	drop expand_ind mark_ind
}

keep if inlist(city, "Chicago", "New York City", "San Francisco")

rename emp_incq1_apr2020 pct_change_all 
rename city cityname

keep zcta cityname pct_change_all

*-------------------------------------------------------------------------------
* Export 
*-------------------------------------------------------------------------------

export delimited "${root}/data/derived/Employment/Map - Change in Low-Income Employment, ZIP.csv", replace
project, creates("${root}/data/derived/Employment/Map - Change in Low-Income Employment, ZIP.csv")
